--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Commit 9c913bdb3c2b76ea4d890066d890dd205d2d8e41
Parents : 2da7d53
Author : Ivan <ivan@quad4.io>
Signature : Invalid signer <e46112d44649266d71fe2193e00a4710>, author is <ivan@quad4.io>
Date : 2026-07-16T17:28:40-05:00
feat: optimize schema v51 message-flag backfill to skip empty databases for faster initialization
Changes
4 files changed, 107 insertions(+), 99 deletions(-)
Diff
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 2a89b7b9..40c534b9 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -24,6 +24,7 @@ All notable changes to this project will be documented in this file.
- UI opens sooner: HTTP binds first, Reticulum starts in the background
- Conversations load faster: slim list and thread queries via fields_meta and attachment flags
+- Schema v51 message-flag backfill skips empty databases so fresh init stays fast
- Relay Chat: denser hub UI, announce interval, collapsed system lines, reconnect notices
- Low-memory cleanup and SQLite pragmas under memory pressure
- CI benches use median-of-medians and quieter regression gates
diff --git a/docs/agents/skills/database-migrations-backups/SKILL.md b/docs/agents/skills/database-migrations-backups/SKILL.md
index f51d6b03..bee26be2 100644
--- a/docs/agents/skills/database-migrations-backups/SKILL.md
+++ b/docs/agents/skills/database-migrations-backups/SKILL.md
@@ -13,6 +13,7 @@ Bump schema versions correctly, keep backups and snapshots safe, and never confl
- Engine is SQLite with explicit SQL. No ORM.
- Bump `LATEST_VERSION` in `meshchatx/src/backend/database/schema.py` and add a migration path.
- Test upgrade from an older version when the change is non-trivial.
+- Heavy data backfills in migrations should skip when the target table is empty. Fresh `Database Initialization` benches create empty DBs and still run every migration step.
## Backup and snapshot rules
diff --git a/meshchatx.rsm b/meshchatx.rsm
index 01d3fde3..f2b16eca 100644
Binary files a/meshchatx.rsm and b/meshchatx.rsm differ
diff --git a/meshchatx/src/backend/database/schema.py b/meshchatx/src/backend/database/schema.py
index 7af210a3..6836a0ca 100644
--- a/meshchatx/src/backend/database/schema.py
+++ b/meshchatx/src/backend/database/schema.py
@@ -1412,102 +1412,108 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_lxmf_messages_peer_id "
"ON lxmf_messages(peer_hash, id DESC)",
)
- # Backfill flags for the latest message of each conversation only.
- # That is the row the sidebar query reads. Full-table instr would be
- # too expensive on large histories.
- self._safe_execute(
- """
- UPDATE lxmf_messages SET
- has_image = CASE
- WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
- AND (instr(fields, '"image"') > 0 OR instr(fields, '"0x05"') > 0)
- THEN 1 ELSE 0 END,
- has_audio = CASE
- WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
- AND (instr(fields, '"audio"') > 0 OR instr(fields, '"0x06"') > 0)
- THEN 1 ELSE 0 END,
- has_files = CASE
- WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
- AND (instr(fields, '"file_attachments"') > 0
- OR instr(fields, '"0x07"') > 0)
- THEN 1 ELSE 0 END,
- has_reaction = CASE
- WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
- AND (instr(fields, '"reaction"') > 0 OR instr(fields, '"0x40"') > 0)
- THEN 1 ELSE 0 END,
- has_telemetry = CASE
- WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
- AND (instr(fields, '"telemetry"') > 0
- OR instr(fields, '"0x08"') > 0
- OR instr(fields, '"telemetry_stream"') > 0)
- THEN 1 ELSE 0 END
- WHERE id IN (
- SELECT MAX(id) FROM lxmf_messages
- WHERE peer_hash IS NOT NULL
- GROUP BY peer_hash
- )
- """,
- )
- # Also flag oversized fields rows (almost always attachments). This keeps
- # heavy conversation opens from SELECT-ing multi-MB blobs without meta.
- self._safe_execute(
- """
- UPDATE lxmf_messages SET
- has_image = CASE
- WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
- AND (instr(fields, '"image"') > 0 OR instr(fields, '"0x05"') > 0)
- THEN 1 ELSE 0 END,
- has_audio = CASE
- WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
- AND (instr(fields, '"audio"') > 0 OR instr(fields, '"0x06"') > 0)
- THEN 1 ELSE 0 END,
- has_files = CASE
- WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
- AND (instr(fields, '"file_attachments"') > 0
- OR instr(fields, '"0x07"') > 0)
- THEN 1 ELSE 0 END,
- has_reaction = CASE
- WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
- AND (instr(fields, '"reaction"') > 0 OR instr(fields, '"0x40"') > 0)
- THEN 1 ELSE 0 END,
- has_telemetry = CASE
- WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
- AND (instr(fields, '"telemetry"') > 0
- OR instr(fields, '"0x08"') > 0
- OR instr(fields, '"telemetry_stream"') > 0)
- THEN 1 ELSE 0 END
- WHERE has_image IS NULL
- AND length(COALESCE(fields, '')) > 16384
- """,
- )
- # Small fields can be copied as-is into fields_meta for thread loads.
- self._safe_execute(
- """
- UPDATE lxmf_messages
- SET fields_meta = fields
- WHERE (fields_meta IS NULL OR fields_meta = '')
- AND fields IS NOT NULL AND fields != ''
- AND length(fields) <= 16384
- """,
- )
- # Large attachment blobs: store a tiny synthetic meta from flags.
- self._safe_execute(
- """
- UPDATE lxmf_messages
- SET fields_meta = CASE
- WHEN COALESCE(has_image, 0) = 1 THEN
- '{"image":{"image_type":"png","image_size":0,"image_bytes":null}}'
- WHEN COALESCE(has_audio, 0) = 1 THEN
- '{"audio":{"audio_mode":0,"audio_size":0,"audio_bytes":null}}'
- WHEN COALESCE(has_files, 0) = 1 THEN
- '{"file_attachments":[{"file_name":"attachment","file_size":0,"file_bytes":null}]}'
- WHEN COALESCE(has_reaction, 0) = 1 THEN
- '{"reaction":{}}'
- WHEN COALESCE(has_telemetry, 0) = 1 THEN
- '{"telemetry":{}}'
- ELSE '{}'
- END
- WHERE (fields_meta IS NULL OR fields_meta = '')
- AND fields IS NOT NULL AND length(fields) > 16384
- """,
- )
+ # Fresh installs have an empty messages table. Skip backfill UPDATEs
+ # so Database Initialization stays fast. Upsert fills flags going forward.
+ has_messages = self.provider.fetchone(
+ "SELECT 1 AS ok FROM lxmf_messages LIMIT 1",
+ )
+ if has_messages:
+ # Backfill flags for the latest message of each conversation only.
+ # That is the row the sidebar query reads. Full-table instr would be
+ # too expensive on large histories.
+ self._safe_execute(
+ """
+ UPDATE lxmf_messages SET
+ has_image = CASE
+ WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
+ AND (instr(fields, '"image"') > 0 OR instr(fields, '"0x05"') > 0)
+ THEN 1 ELSE 0 END,
+ has_audio = CASE
+ WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
+ AND (instr(fields, '"audio"') > 0 OR instr(fields, '"0x06"') > 0)
+ THEN 1 ELSE 0 END,
+ has_files = CASE
+ WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
+ AND (instr(fields, '"file_attachments"') > 0
+ OR instr(fields, '"0x07"') > 0)
+ THEN 1 ELSE 0 END,
+ has_reaction = CASE
+ WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
+ AND (instr(fields, '"reaction"') > 0 OR instr(fields, '"0x40"') > 0)
+ THEN 1 ELSE 0 END,
+ has_telemetry = CASE
+ WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
+ AND (instr(fields, '"telemetry"') > 0
+ OR instr(fields, '"0x08"') > 0
+ OR instr(fields, '"telemetry_stream"') > 0)
+ THEN 1 ELSE 0 END
+ WHERE id IN (
+ SELECT MAX(id) FROM lxmf_messages
+ WHERE peer_hash IS NOT NULL
+ GROUP BY peer_hash
+ )
+ """,
+ )
+ # Also flag oversized fields rows (almost always attachments). This keeps
+ # heavy conversation opens from SELECT-ing multi-MB blobs without meta.
+ self._safe_execute(
+ """
+ UPDATE lxmf_messages SET
+ has_image = CASE
+ WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
+ AND (instr(fields, '"image"') > 0 OR instr(fields, '"0x05"') > 0)
+ THEN 1 ELSE 0 END,
+ has_audio = CASE
+ WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
+ AND (instr(fields, '"audio"') > 0 OR instr(fields, '"0x06"') > 0)
+ THEN 1 ELSE 0 END,
+ has_files = CASE
+ WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
+ AND (instr(fields, '"file_attachments"') > 0
+ OR instr(fields, '"0x07"') > 0)
+ THEN 1 ELSE 0 END,
+ has_reaction = CASE
+ WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
+ AND (instr(fields, '"reaction"') > 0 OR instr(fields, '"0x40"') > 0)
+ THEN 1 ELSE 0 END,
+ has_telemetry = CASE
+ WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
+ AND (instr(fields, '"telemetry"') > 0
+ OR instr(fields, '"0x08"') > 0
+ OR instr(fields, '"telemetry_stream"') > 0)
+ THEN 1 ELSE 0 END
+ WHERE has_image IS NULL
+ AND length(COALESCE(fields, '')) > 16384
+ """,
+ )
+ # Small fields can be copied as-is into fields_meta for thread loads.
+ self._safe_execute(
+ """
+ UPDATE lxmf_messages
+ SET fields_meta = fields
+ WHERE (fields_meta IS NULL OR fields_meta = '')
+ AND fields IS NOT NULL AND fields != ''
+ AND length(fields) <= 16384
+ """,
+ )
+ # Large attachment blobs: store a tiny synthetic meta from flags.
+ self._safe_execute(
+ """
+ UPDATE lxmf_messages
+ SET fields_meta = CASE
+ WHEN COALESCE(has_image, 0) = 1 THEN
+ '{"image":{"image_type":"png","image_size":0,"image_bytes":null}}'
+ WHEN COALESCE(has_audio, 0) = 1 THEN
+ '{"audio":{"audio_mode":0,"audio_size":0,"audio_bytes":null}}'
+ WHEN COALESCE(has_files, 0) = 1 THEN
+ '{"file_attachments":[{"file_name":"attachment","file_size":0,"file_bytes":null}]}'
+ WHEN COALESCE(has_reaction, 0) = 1 THEN
+ '{"reaction":{}}'
+ WHEN COALESCE(has_telemetry, 0) = 1 THEN
+ '{"telemetry":{}}'
+ ELSE '{}'
+ END
+ WHERE (fields_meta IS NULL OR fields_meta = '')
+ AND fields IS NOT NULL AND length(fields) > 16384
+ """,
+ )
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────